import numpy as np
import logging
import pandas as pd
from scipy.optimize import differential_evolution, minimize,NonlinearConstraint
from dataclasses import dataclass
from typing import List, Tuple, Dict, Any
import pyodbc
import os
from datetime import datetime
import multiprocessing
import time
import llm.llmClientPdfHandler as llmClientPdfHandler
import optimize.CustomsRAGSystemTest as CustomsRAGSystemTest

# 配置日志 使用这个，完成自动填单
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(levelname)s - %(message)s',
    handlers=[
        logging.FileHandler('../optimization.log'),
        logging.StreamHandler()
    ]
)
logger = logging.getLogger(__name__)


@dataclass
class Product:
    """产品信息数据类"""
    name: str
    price: float # 产品数量
    tariff_coef: float
    base_tax: float #基础关税
    add_tax: float #附件关税
    hs_code: str # 海关编码
    quantity: float
    volume: float
    weight: float
    piece_bounds: Tuple[float, float]
    quantity_bounds: Tuple[float, float]
    volume_bounds: Tuple[float, float]
    weight_bounds: Tuple[float, float]
    product_weight_bounds: Tuple[float, float]


class TwoStageProductOptimizer:
    """两阶段产品参数优化器：先全局搜索再局部精确优化，并支持结果导出到Excel"""

    def __init__(self, products: List[Product], config=None, de_params=None):
        """
        初始化优化器

        参数:
            products: 产品列表
        """
        self.products = products
        self.n_products = len(products)

        # 优化结果
        self.initial_solution = None  # 第一阶段结果
        self.final_solution = None  # 第二阶段结果
        self.best_violation = float('inf')

        # 约束配置 - 严格约束
        self.constraint_config = config

        # 第一阶段：差分进化算法参数（全局搜索）
        self.de_params = de_params

        # 第二阶段：局部优化算法参数（精确求解）
        self.minimize_method = 'SLSQP'  # 适合处理约束的局部优化方法
        self.minimize_options = {
            'maxiter': 1000,
            'ftol': 1e-10,
            'eps': 1e-8
        }

        logger.info(f"初始化两阶段产品优化器，共{self.n_products}种产品")
        logger.info(f"严格约束: 数量总和={self.constraint_config['quantity_sum']}, "
                    f"体积总和={self.constraint_config['volume_sum']}, "
                    f"重量总和={self.constraint_config['weight_sum']}")

    def _get_variable_bounds(self) -> List[Tuple[float, float]]:
        """获取所有变量的边界"""
        bounds = []

        # 产品件数边界
        for p in self.products:
            bounds.append(p.piece_bounds)

        # 数量边界
        for p in self.products:
            bounds.append(p.quantity_bounds)

        # 体积边界
        for p in self.products:
            bounds.append(p.volume_bounds)

        # 重量边界
        for p in self.products:
            bounds.append(p.weight_bounds)

        # 产品重量边界
        for p in self.products:
            bounds.append(p.product_weight_bounds)

        return bounds

    def _parse_solution(self, x: np.ndarray) -> Dict[str, Any]:
        """解析优化结果"""
        n = self.n_products
        return {
            'pieces': x[:n],
            'quantity': x[n:2 * n],
            'volume': x[2 * n:3 * n],
            'weight': x[3 * n:4 * n],
            'product_weight': x[4 * n:5 * n]
        }

    def _calculate_total_value(self, pieces: np.ndarray) -> float:
        """计算产品总价值"""
        prices = np.array([p.price for p in self.products])
        return np.sum(pieces * prices)

    def _calculate_tariff_sum(self, pieces: np.ndarray) -> float:
        """计算关税总和"""
        per_tariffs = self._calculate_per_tariffs(pieces)
        return np.sum(per_tariffs)

    def _calculate_per_tariffs(self, pieces):
        tariffs = np.array([p.tariff_coef for p in self.products])
        prices = np.array([p.price for p in self.products])
        per_tariffs = tariffs * pieces * prices / 100
        return per_tariffs

    def _calculate_constraint_violation(self, x: np.ndarray) -> float:
        """计算所有约束的违反程度，整合到一个值中"""
        parsed = self._parse_solution(x)
        violation = 0

        # 1. 数量总和约束违反（严格约束，高权重）
        quantity_sum = np.sum(parsed['quantity'])
        quantity_target = self.constraint_config['quantity_sum']
        violation += 500 * abs(quantity_sum - quantity_target)

        # 2. 体积总和约束违反（严格约束，高权重）
        volume_sum = np.sum(parsed['volume'])
        volume_target = self.constraint_config['volume_sum']
        violation += 500 * abs(volume_sum - volume_target)

        # 3. 重量总和约束违反（严格约束，高权重）
        weight_sum = np.sum(parsed['weight'])
        weight_target = self.constraint_config['weight_sum']
        violation += 500 * abs(weight_sum - weight_target)

        # 4. 总价值约束违反
        parsed_pieces_ = parsed['pieces']
        total_value = self._calculate_total_value(parsed_pieces_)
        v_min, v_max = self.constraint_config['total_value']
        if total_value < v_min:
            violation += 100 * (v_min - total_value)**2
        elif total_value > v_max:
            violation += 100 * (total_value - v_max)**2


        # 5. 关税约束违反
        tariff_sum = self._calculate_tariff_sum(parsed_pieces_)
        t_min, t_max = self.constraint_config['tariff_sum']
        if tariff_sum < t_min:
            violation += (t_min - tariff_sum)
        elif tariff_sum > t_max:
            violation += (tariff_sum - t_max)

        # 6. 重量/产品重量比率约束违反
        for i in range(self.n_products):
            ratio = parsed['weight'][i] / np.clip(parsed['product_weight'][i], 1e-8, np.inf)
            violation += 10 * abs(ratio - parsed_pieces_[i])

        # 7. 变量边界约束违反（确保解在合法范围内）
        bounds = self._get_variable_bounds()
        for i, (lower, upper) in enumerate(bounds):
            if x[i] < lower:
                violation += 1000 * (lower - x[i])
            elif x[i] > upper:
                violation += 1000 * (x[i] - upper)

        # 箱子重量相差不能过大
        std_per_box_weight = np.std(parsed['weight'] / parsed['quantity'])

        if std_per_box_weight > 3:
            violation = violation + std_per_box_weight**2


        #产品申报数量大于箱数
        pq = parsed_pieces_ - parsed['quantity']
        pq_sum = sum([x for x in pq if x < 0])
        if pq_sum < 0:
            violation += 1000 * pq_sum ** 2

        # 关税约束
        max_tariff = self.constraint_config['tariff_sum'][1]
        min_tariff = self.constraint_config['tariff_sum'][0]
        total_tariff = self._calculate_tariff_sum(parsed_pieces_)
        #超过关税边界则加大处罚
        if total_tariff >= max_tariff-200:
            violation += 5 * (total_tariff - min_tariff)**2
        if total_tariff <= min_tariff+50:
            violation += 5 * (total_tariff - min_tariff)**2
        #尽量靠近最小关税附近
        violation +=  abs(total_tariff - min_tariff)*10*2
        prices = np.array([p.price for p in self.products])
        sorted_pairs = sorted(zip(prices, parsed_pieces_))
        # sorted_keys, sorted_values = zip(*sorted_pairs)
        # 价格高的数量要少
        quantities_prices = 0
        for i, pair in enumerate(sorted_pairs):
            quantities_prices += (pair[1]) * i
        # 产品数量和箱数相差越大越好
        # qpr = (np.sum(parsed['quantity']) - np.sum(parsed_pieces_))*100
        # if violation + qpr > 0:
        #     violation = violation + qpr
        return violation +quantities_prices

    # @staticmethod
    # @jit(nopython=True)
    def _stage1_objective(self, x: np.ndarray) -> float:
        """第一阶段目标函数：总价值接近5000 + 约束违反惩罚"""
        start_time = time.time()

        parsed = self._parse_solution(x)
        pieces_ = parsed['pieces']
        total_sales = self._calculate_total_value(pieces_)

        # 主要目标：总价值接近5000
        max_total_sales = self.constraint_config['total_value'][1]
        target_penalty = abs(total_sales - max_total_sales) * 2

        # 销售额范围约束
        # min_sales = self.constraint_config['total_value'][0]
        # max_sales = self.constraint_config['total_value'][1]
        # penalty = 0
        # if total_sales <= min_sales:
        #     penalty += 10 * (min_sales - total_sales)**2
        # elif total_sales > max_sales:
        #     penalty += 10 * (total_sales - max_sales)**2



        # 约束违反惩罚
        constraint_penalty = self._calculate_constraint_violation(x)
        end_time = time.time()
        # duration = end_time - start_time
        # logger.info( f"方法 stage1 执行耗时: {duration:.4f} 秒")


        return target_penalty + constraint_penalty

    def _stage2_objective(self, x: np.ndarray) -> float:
        """第二阶段目标函数：更注重约束满足和精度"""
        parsed = self._parse_solution(x)
        total_value = self._calculate_total_value(parsed['pieces'])
        max_total_sales = self.constraint_config['total_value'][1]
        # 主要目标：接近5000（权重降低，因为已经找到大致位置）
        target_penalty = abs(total_value - max_total_sales)

        # 约束违反惩罚（权重提高，注重精确满足）
        constraint_penalty = self._calculate_constraint_violation(x) * 2

        return target_penalty + constraint_penalty


    def stage2_constraint(self, x: np.ndarray):
        """目标函数 - 只优化单价和数量"""
        parse_solution = self._parse_solution(x)
        # 总和约束差异（标量）
        quantity_dif = np.sum(parse_solution['quantity']) - self.constraint_config['quantity_sum']
        volume_dif = np.sum(parse_solution['volume']) - self.constraint_config['volume_sum']
        weight_dif = np.sum(parse_solution['weight']) - self.constraint_config['weight_sum']

        # 产品级别的约束（数组）
        pieces_quantity_compare = parse_solution['pieces'] - parse_solution['quantity']
        # 要求产品申报数量 >= 箱数，所以 pieces - quantity >= 0
        # return parse_solution['pieces'] - parse_solution['quantity']
        # 组合所有约束
        constraints = []

        # 添加总和约束
        constraints.extend([quantity_dif, volume_dif, weight_dif])

        # 添加产品级别的约束
        constraints.extend(pieces_quantity_compare)

        return np.array(constraints)

    def optimize(self, n_attempts: int = 5) -> bool:
        """
        执行两阶段优化

        参数:
            n_attempts: 第一阶段尝试次数

        返回:
            是否找到可行解
        """
        logger.info("开始两阶段优化流程")
        bounds = self._get_variable_bounds()

        # 第一阶段：使用differential_evolution找到近似解
        logger.info(f"第一阶段：差分进化全局搜索，共尝试{n_attempts}次")
        best_initial_violation = float('inf')
        n_cores = 5
        for attempt in range(n_attempts):
            logger.info(f"第一阶段尝试 {attempt + 1}/{n_attempts}")

            try:
                result = differential_evolution(
                    self._stage1_objective,
                    bounds,
                    strategy = 'best1bin',
                    disp=True,
                    init='random',
                    workers=n_cores,  # 关键：使用多进程
                    updating='deferred',  # 并行时必须设置
                    seed=attempt, **self.de_params
                )

                # 计算约束违反程度
                violation = self._calculate_constraint_violation(result.x)

                # 记录最佳初始解
                if violation < best_initial_violation:
                    best_initial_violation = violation
                    self.initial_solution = result
                    logger.info(f"第一阶段找到更好解，约束违反程度: {best_initial_violation:.6f}")

                # 如果找到足够好的解，提前退出
                if best_initial_violation < 1e-3:
                    logger.info("第一阶段找到足够好的解，提前结束")
                    break

            except Exception as e:
                logger.error(f"第一阶段尝试 {attempt + 1} 失败: {str(e)}")

        if not self.initial_solution:
            logger.error("第一阶段未能找到有效解，无法进行第二阶段优化")
            return False

        logger.info(f"第一阶段优化完成，最佳约束违反程度: {best_initial_violation:.6f}")
        logger.info(f"第一阶段最佳目标函数值: {self.initial_solution.fun:.6f}")

        # 第二阶段：使用minimize进行精确优化
        logger.info(f"第二阶段：使用{self.minimize_method}进行局部精确优化")

        try:

            # 修改约束边界
            # 对于总和约束：quantity_dif, volume_dif, weight_dif 应该等于0
            # 对于产品级别约束：pieces - quantity >= 0
            n_constraints = 3 + self.n_products  # 3个总和约束 + n个产品约束
            lb = np.zeros(n_constraints)
            ub = np.full(n_constraints, np.inf)  # 总和约束必须等于0，产品约束可以大于等于0

            # 设置前3个约束的上下界为0（必须严格等于）
            lb[:3] = 0
            ub[:3] = 0

            constraints = NonlinearConstraint(self.stage2_constraint, lb, ub)
            # constraints = NonlinearConstraint(self.stage2_constraint, 0, 0)  # lb=0, ub=0 表示等于10
            # 使用第一阶段结果作为初始点
            result = minimize(
                self._stage2_objective,
                self.initial_solution.x,
                method=self.minimize_method,
                constraints = constraints,
                bounds=bounds,
                options=self.minimize_options
            )

            self.final_solution = result
            final_violation = self._calculate_constraint_violation(result.x)
            logger.info(f"第二阶段优化完成，约束违反程度: {final_violation:.9f}")
            logger.info(f"第二阶段目标函数值: {result.fun:.6f}")

            return final_violation < 1e-6

        except Exception as e:
            logger.error(f"第二阶段优化失败: {str(e)}")
            return False

    def save_results_to_excel(self, filename: str = None) -> None:
        """
        将优化结果保存到Excel文件

        参数:
            filename: 文件名，若为None则自动生成包含时间戳的文件名
        """
        if not self.initial_solution or not self.final_solution:
            logger.warning("没有完整的优化结果可保存")
            return

        if not os.path.exists("../优化结果"):
            os.makedirs("../优化结果", exist_ok=True)
        # 生成包含时间戳的默认文件名
        if filename is None:
            timestamp = datetime.now().strftime("%Y%m%d_%H%M%S")
            filename = f"优化结果/optimization_results_{timestamp}.xlsx"

        # 解析结果
        initial_parsed = self._parse_solution(self.initial_solution.x)
        final_parsed = self._parse_solution(self.final_solution.x)

        # 计算关键指标
        initial_value = self._calculate_total_value(initial_parsed['pieces'])
        final_value = self._calculate_total_value(final_parsed['pieces'])

        initial_tariff = self._calculate_tariff_sum(initial_parsed['pieces'])
        final_tariff = self._calculate_tariff_sum(final_parsed['pieces'])
        max_total_sales = self.constraint_config['total_value'][1]
        # 创建Excel写入器
        with pd.ExcelWriter(filename, engine='openpyxl') as writer:
            # 1. 汇总信息表
            summary_data = {
                '指标': [
                    '产品总价值', f'与目标值{max_total_sales}的差距', '关税总和',
                    '约束违反程度', '数量总和', '体积总和', '重量总和'
                ],
                '第一阶段结果': [
                    f"{initial_value:.2f}",
                    f"{abs(initial_value - max_total_sales):.2f}",
                    f"{initial_tariff:.2f}",
                    f"{self._calculate_constraint_violation(self.initial_solution.x):.6f}",
                    f"{np.sum(initial_parsed['quantity']):.4f}",
                    f"{np.sum(initial_parsed['volume']):.4f}",
                    f"{np.sum(initial_parsed['weight']):.4f}"
                ],
                '第二阶段结果': [
                    f"{final_value:.2f}",
                    f"{abs(final_value - max_total_sales):.2f}",
                    f"{final_tariff:.2f}",
                    f"{self._calculate_constraint_violation(self.final_solution.x):.9f}",
                    f"{np.sum(final_parsed['quantity']):.4f}",
                    f"{np.sum(final_parsed['volume']):.4f}",
                    f"{np.sum(final_parsed['weight']):.4f}"
                ],
                '目标值/范围': [
                    f"{self.constraint_config['total_value'][0]}-{self.constraint_config['total_value'][1]}",
                    "0",
                    f"≤{self.constraint_config['tariff_sum'][1]}",
                    "0",
                    f"{self.constraint_config['quantity_sum']}",
                    f"{self.constraint_config['volume_sum']}",
                    f"{self.constraint_config['weight_sum']}"
                ]
            }

            summary_df = pd.DataFrame(summary_data)
            summary_df.to_excel(writer, sheet_name='汇总信息', index=False)

            # 2. 产品详细信息表
            product_data = []
            final_pieces = np.round(final_parsed['pieces']).astype(int)
            final_quantity = np.round(final_parsed['quantity']).astype(int)
            final_volume = np.round(final_parsed['volume'], 4)
            final_weight = np.round(final_parsed['weight'], 4)
            final_product_weight = np.round(final_parsed['product_weight'], 4)

            for i in range(self.n_products):
                product = self.products[i]
                product_value = final_pieces[i] * product.price
                weight_ratio = final_weight[i] / final_product_weight[i] if final_product_weight[i] > 0 else 0

                product_data.append({
                    '产品名称': product.name,
                    '海关编码': product.hs_code,#
                    '基础关税': product.base_tax,#
                    '附加关税': product.add_tax,#
                    '件数CTNS': final_quantity[i],
                    '产品申报数量': final_pieces[i],
                    '收货实重': final_weight[i],
                    '方数': final_volume[i],
                    '产品申报单价': product.price,
                    '总价(USD)': product_value,
                    '税金': product.tariff_coef*product_value/100,
                    '产品均重':final_weight[i]/final_pieces[i],
                    '箱重':final_weight[i]/final_quantity[i],
                    '每箱产品数':final_pieces[i]/final_quantity[i]
                })

            products_df = pd.DataFrame(product_data)
            products_df.to_excel(writer, sheet_name='产品详细信息', index=False)

            product_constraints = pd.DataFrame(self.products)
            product_constraints.to_excel(writer, sheet_name='产品约束条件', index=False)

            # 3. 约束条件表
            constraints_data = {
                '约束类型': [
                    '数量总和', '体积总和', '重量总和',
                    '产品总价值', '关税总和', '重量比率差异'
                ],
                '约束值/范围': [
                    f"{self.constraint_config['quantity_sum']}",
                    f"{self.constraint_config['volume_sum']}",
                    f"{self.constraint_config['weight_sum']}",
                    f"{self.constraint_config['total_value'][0]}-{self.constraint_config['total_value'][1]}",
                    f"0-{self.constraint_config['tariff_sum'][1]}",
                    f"{self.constraint_config['weight_ratio'][0]}-{self.constraint_config['weight_ratio'][1]}"
                ],
                '实际值': [
                    f"{np.sum(final_parsed['quantity']):.4f}",
                    f"{np.sum(final_parsed['volume']):.4f}",
                    f"{np.sum(final_parsed['weight']):.4f}",
                    f"{final_value:.2f}",
                    f"{final_tariff:.2f}",
                    f"{self._calculate_weight_ratio_violation(self.final_solution.x):.6f}"
                ],
                '是否满足': [
                    "是" if abs(
                        np.sum(final_parsed['quantity']) - self.constraint_config['quantity_sum']) < 1e-6 else "否",
                    "是" if abs(np.sum(final_parsed['volume']) - self.constraint_config['volume_sum']) < 1e-6 else "否",
                    "是" if abs(np.sum(final_parsed['weight']) - self.constraint_config['weight_sum']) < 1e-6 else "否",
                    "是" if (self.constraint_config['total_value'][0] <= final_value <=
                             self.constraint_config['total_value'][1]) else "否",
                    "是" if (final_tariff <= self.constraint_config['tariff_sum'][1]) else "否",
                    "是" if (self._calculate_weight_ratio_violation(self.final_solution.x) <=
                             self.constraint_config['weight_ratio'][1]) else "否"
                ]
            }

            constraints_df = pd.DataFrame(constraints_data)
            constraints_df.to_excel(writer, sheet_name='约束条件检查', index=False)

        logger.info(f"优化结果已成功保存到Excel文件: {filename}")

    def _calculate_weight_ratio_violation(self, x: np.ndarray) -> float:
        """计算重量比率违反程度"""
        parsed = self._parse_solution(x)
        diff = 0
        for i in range(self.n_products):
            ratio = parsed['weight'][i] / np.clip(parsed['product_weight'][i], 1e-8, np.inf)
            diff += abs(ratio - parsed['pieces'][i])
        return diff

    def print_results(self):
        """打印两阶段优化结果，对比优化前后的变化"""
        if not self.initial_solution or not self.final_solution:
            logger.warning("没有完整的优化结果可显示")
            return

        # 解析两个阶段的结果
        initial_parsed = self._parse_solution(self.initial_solution.x)
        final_parsed = self._parse_solution(self.final_solution.x)

        # 计算关键指标
        initial_value = self._calculate_total_value(initial_parsed['pieces'])
        final_value = self._calculate_total_value(final_parsed['pieces'])

        initial_tariff = self._calculate_tariff_sum(initial_parsed['pieces'])
        final_tariff = self._calculate_tariff_sum(final_parsed['pieces'])

        # 计算总和（用于验证严格约束）
        initial_quantity_sum = np.sum(initial_parsed['quantity'])
        final_quantity_sum = np.sum(final_parsed['quantity'])

        initial_volume_sum = np.sum(initial_parsed['volume'])
        final_volume_sum = np.sum(final_parsed['volume'])

        initial_weight_sum = np.sum(initial_parsed['weight'])
        final_weight_sum = np.sum(final_parsed['weight'])

        # 检查严格约束是否满足
        initial_violation = self._calculate_constraint_violation(self.initial_solution.x)
        final_violation = self._calculate_constraint_violation(self.final_solution.x)

        sum_constraints_satisfied = (
                abs(final_quantity_sum - self.constraint_config['quantity_sum']) < 1e-6 and
                abs(final_volume_sum - self.constraint_config['volume_sum']) < 1e-6 and
                abs(final_weight_sum - self.constraint_config['weight_sum']) < 1e-6
        )

        if sum_constraints_satisfied and final_violation < 1e-6:
            logger.info("\n优化成功！最终方案满足所有严格约束：")
        else:
            logger.warning(f"\n最终解仍有约束违反，违反程度: {final_violation:.9f}")
        max_total_sales = self.constraint_config['total_value'][1]
        # 打印优化前后对比
        logger.info("\n===== 优化前后对比 =====")
        logger.info(f"产品总价值：{initial_value:.2f} → {final_value:.2f} (目标：{max_total_sales})")
        logger.info(f"与目标值差距：{abs(initial_value - max_total_sales):.2f} → {abs(final_value - max_total_sales):.2f}")
        logger.info(
            f"关税总和：{initial_tariff:.2f} → {final_tariff:.2f} (上限：{self.constraint_config['tariff_sum'][1]})")
        logger.info(f"约束违反程度：{initial_violation:.6f} → {final_violation:.9f}")

        # 打印严格约束满足情况
        logger.info("\n===== 严格约束满足情况 =====")
        logger.info(
            f"数量总和：{initial_quantity_sum:.4f} → {final_quantity_sum:.4f} (目标：{self.constraint_config['quantity_sum']})")
        logger.info(
            f"体积总和：{initial_volume_sum:.4f} → {final_volume_sum:.4f} (目标：{self.constraint_config['volume_sum']})")
        logger.info(
            f"重量总和：{initial_weight_sum:.4f} → {final_weight_sum:.4f} (目标：{self.constraint_config['weight_sum']})")

        # 打印最终方案的各产品详细参数
        logger.info("\n===== 最终方案详细参数 =====")
        final_pieces = np.round(final_parsed['pieces']).astype(int)
        final_quantity = np.round(final_parsed['quantity']).astype(int)
        final_volume = np.round(final_parsed['volume'], 4)
        final_weight = np.round(final_parsed['weight'], 4)
        final_product_weight = np.round(final_parsed['product_weight'], 4)

        for i in range(self.n_products):
            if final_pieces[i] > 0:
                logger.info(f"{self.products[i].name}:")
                logger.info(f"  单价: {self.products[i].price}")
                logger.info(f"  产品件数: {final_pieces[i]}")
                logger.info(f"  数量: {final_quantity[i]}")
                logger.info(f"  体积: {final_volume[i]}")
                logger.info(f"  重量: {final_weight[i]}")
                logger.info(f"  产品重量: {final_product_weight[i]}")
                logger.info(f"  产品价值: {final_pieces[i] * self.products[i].price}")
                logger.info(f"  重量/产品重量比率: {final_weight[i] / final_product_weight[i]:.2f}\n")



def read_excel_data(file_path):
    """
    读取Excel文件的Sheet1数据

    参数:
    file_path (str): Excel文件路径

    返回:
    pandas.DataFrame: 包含Sheet1数据的DataFrame
    """
    try:
        # 检查文件是否存在
        if not os.path.exists(file_path):
            raise FileNotFoundError(f"文件 '{file_path}' 不存在")

        # 读取Sheet1数据
        df = pd.read_excel(file_path, sheet_name='Sheet1')

        logger.info(f"成功读取文件: {file_path}")
        logger.info(f"数据形状: {df.shape} (行数: {df.shape[0]}, 列数: {df.shape[1]})")
        logger.info("\n前5行数据:")
        logger.info(df.head())

        return df

    except Exception as e:
        logger.info(f"读取文件时出错: {e}")
        return None

# 自定义函数返回(min, max)元组
def min_max_tuple(series):
    return (series.min()/2, series.max()*2)


def get_latest_customs_data_pandas(db_path, name_list):
    """
    使用pandas从Access数据库中读取"清关资料"表的最新数据

    Args:
        db_path (str): Access数据库文件路径

    Returns:
        dict: 最新一条数据的字典，如果没有数据则返回None
    """
    try:
        # 创建数据库连接字符串
        conn_str = (
            r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};'
            f'DBQ={db_path};'
        )

        # 建立数据库连接
        conn = pyodbc.connect(conn_str)
        # 构建IN子句中的占位符
        placeholders = ', '.join(['?' for _ in name_list])

        # SQL查询语句
        sql_query = f"""
        SELECT * FROM 清关资料 where 英文品名 in ({placeholders})
        ORDER BY 导入时间 DESC
        """
        # 读取数据到pandas DataFrame
        df = pd.read_sql(sql_query, conn, params=name_list)

        # 关闭连接
        conn.close()
        # 正确做法3：先计算聚合结果，再合并回原数据
        df['产品重量'] = (df['收货实重']-df['件数CTNS'])/df['产品申报数量']
        df3 = df.copy()

        # 计算约束统计量
        constraint_stats = df3.groupby('英文品名').agg(
            min_div_2=('产品申报单价', lambda x: x.min()),
            max_mul_2=('产品申报单价', lambda x: x.max()),
            # min_product_volume=('产品体积', lambda x: x.min()),
            # max_product_volume=('产品体积', lambda x: x.max()),
            min_product_weight=('产品重量', lambda x: x.min()),
            max_product_weight=('产品重量', lambda x: x.max())
        ).reset_index()
        # 合并回原数据
        df = df3.merge(constraint_stats, on='英文品名', how='left')
        df = df.drop_duplicates(subset=['英文品名'])

        return df

    except pyodbc.Error as e:
        logger.info(f"数据库连接错误: {e}")
        return None
    except Exception as e:
        logger.info(f"发生错误: {e}")
        return None


def get_one_random_data_from_db(db_path, not_in_list):
    """
    使用pandas从Access数据库中读取"清关资料"表的最新数据

    Args:
        db_path (str): Access数据库文件路径

    Returns:
        dict: 最新一条数据的字典，如果没有数据则返回None
    """
    try:
        # 创建数据库连接字符串
        conn_str = (
            r'DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};'
            f'DBQ={db_path};'
        )

        # 建立数据库连接
        conn = pyodbc.connect(conn_str)
        # 构建IN子句中的占位符
        placeholders = ', '.join(['?' for _ in not_in_list])

        # SQL查询语句
        sql_query = f"""
        SELECT  TOP 1 * FROM 清关资料 where 英文品名 not  in ({placeholders})  ORDER BY RND(TIMER())
        """
        # 读取数据到pandas DataFrame
        df = pd.read_sql(sql_query, conn, params=not_in_list)

        # 关闭连接
        conn.close()
        # 正确做法3：先计算聚合结果，再合并回原数据
        df['产品重量'] = (df['收货实重']-df['件数CTNS'])/df['产品申报数量']
        df3 = df.copy()

        # 计算约束统计量
        constraint_stats = df3.groupby('英文品名').agg(
            min_div_2=('产品申报单价', lambda x: x.min()),
            max_mul_2=('产品申报单价', lambda x: x.max()),
            # min_product_volume=('产品体积', lambda x: x.min()),
            # max_product_volume=('产品体积', lambda x: x.max()),
            min_product_weight=('产品重量', lambda x: x.min()),
            max_product_weight=('产品重量', lambda x: x.max())
        ).reset_index()
        # 合并回原数据
        df = df3.merge(constraint_stats, on='英文品名', how='left')
        df = df.drop_duplicates(subset=['英文品名'])

        return df

    except pyodbc.Error as e:
        logger.info(f"数据库连接错误: {e}")
        return None
    except Exception as e:
        logger.info(f"发生错误: {e}")
        return None

def update_excel_data_advanced(df_a, df_b, update_columns=None):
    """
    高级版本：可以指定需要更新的列

    Parameters:
    file_a: 原始Excel文件路径
    file_b: 包含更新数据的Excel文件路径
    update_columns: 需要更新的列名列表，如果为None则更新所有列（除英文品名外）
    output_file: 输出文件路径
    """


    # 如果没有指定更新列，则更新除英文品名外的所有列
    if update_columns is None:
        update_columns = [col for col in df_a.columns if col != '英文品名']

    logger.info(f"将更新以下列: {update_columns}")

    # 方法2：使用迭代更新（更直观的方法）
    updated_count = 0

    for idx_b, row_b in df_b.iterrows():
        english_name = row_b['英文品名']

        # 在df_a中查找相同英文品名的行
        mask = df_a['英文品名'] == english_name

        if mask.any():
            # 更新找到的行
            for col in update_columns:
                if col in row_b and pd.notna(row_b[col]):
                    df_a.loc[mask, col] = row_b[col]

            updated_count += 1

    logger.info(f"成功更新了{updated_count}条记录")

    return df_a



def auto_optimize(excel_data, excel_path, constraint_config=None, de_params=None):
    """主函数：创建产品并执行两阶段优化"""
    # 定义产品
    products = []
    products.clear()
    for index, row in excel_data.iterrows():
        products.append(Product(
            name=row['英文品名'],
            price=row['产品申报单价'],
            tariff_coef=0.125+row['基础关税']+row['附加关税'],
            base_tax= row['基础关税'], # 基础关税
            add_tax =row['附加关税'],  # 附件关税
            hs_code= row['产品海关编码'],  # 海关编码
            quantity=row['件数CTNS'],
            volume=row['方数'],
            weight=row['收货实重'],
            piece_bounds=(1, 10000),#产品申报数量
            # piece_bounds=(row['min_div_2'], row['max_mul_2']+1),
            quantity_bounds=(1, constraint_config['quantity_sum']),
            volume_bounds=(1, constraint_config['volume_sum']),
            weight_bounds=(1, constraint_config['weight_sum']),
            product_weight_bounds=(row['min_product_weight'], row['max_product_weight'])
        ))
    # 创建优化器并执行两阶段优化
    optimizer = TwoStageProductOptimizer(products, constraint_config,de_params=de_params)
    success = optimizer.optimize(n_attempts=1)

    # 打印结果
    optimizer.print_results()

    # 获取目录路径
    # dir_path = os.path.dirname(excel_path)
    # filename = os.path.basename(excel_path)
    # save_path = os.path.join(dir_path, f"optimization_{filename}")
    save_path = excel_path
    # 保存结果到Excel
    if success:
        optimizer.save_results_to_excel(save_path)
    else:
        logger.info("由于优化未完全成功，仍将保存现有结果供参考")
        optimizer.save_results_to_excel(save_path)

    return 0 if success else 1


def main_handle(db_path:str=None, pdf_path:str=None, de_params:dict=None,add_constraint_config:dict=None):
    # global df, weight
    pdf_info = llmClientPdfHandler.llm_extract_pdf_info(pdf_path)
    pdf_goods = pdf_info['result']['goods']
    excel_data = []
    best_match_result = CustomsRAGSystemTest.batch_processing_example(pdf_goods)
    for g in pdf_goods:
        best_match_key = best_match_result[g].generated_response
        if best_match_key:
            #     products_ = best_match_result[g]['retrieved_products']
            #     for product in products_:
            #         if product['英文品名'] == best_match_key:
            excel_data.append({'英文品名': best_match_key})
            logger.info(f" find the best match:{g}:{best_match_key}")
        else:
            logger.error(f"could not find the best match:{g}")
    # excel_data = read_excel_data(excel_path)
    df = pd.DataFrame(excel_data)
    name_list = df['英文品名'].tolist()
    db_data = get_latest_customs_data_pandas(db_path, name_list)
    columns_to_update = ['产品材质', '件数CTNS', '产品申报数量', '净重KGS', '收货实重', '方数', '总价', '产品海关编码',
                         '基础关税', '附加关税', '产品申报单价', 'max_mul_2', 'min_div_2', 'min_product_weight',
                         'max_product_weight']
    while len(name_list) < 6:
        d = get_one_random_data_from_db(db_path, name_list)
        # db_data.update(d)
        add_size = 6 - len(name_list)
        add_e = d['英文品名'].tolist()[:add_size]
        for e in add_e:
            name_list.append(e)
            excel_data.append({'英文品名': e})
    # 使用数据库的数据更新excel中的数据
    df = pd.DataFrame(excel_data)
    db_data = get_latest_customs_data_pandas(db_path, name_list)
    update_excel_data_advanced(df, db_data, columns_to_update)
    packages, weight, volume = pdf_info['result']['packages'], pdf_info['result']['weight'], pdf_info['result'][
        'volume']
    # main(excel_data,pdf_info['packages'],pdf_info['weight'],pdf_info['volume'])
    dir_path = os.path.dirname(pdf_path)
    # filename = os.path.basename(pdf_path)
    filename_without_ext = os.path.splitext(os.path.basename(pdf_path))[0]
    excel_path = os.path.join(dir_path, f"optimization_{filename_without_ext}.xlsx")
    constraint_config = {
        'quantity_sum': packages,  # 数量总和必须严格等于1000
        'volume_sum': volume,  # 体积总和必须严格等于150
        'weight_sum': weight,  # 实重 重量总和必须严格等于180
        'total_value': (5000, 5500),  # 产品总价值范围
        'tariff_sum': (1300, 1500),  # 关税总和范围
        'weight_ratio': (0, 1.0)  # 重量比率差异范围
    }
    constraint_config.update(add_constraint_config)
    auto_optimize(df, excel_path, constraint_config=constraint_config,de_params=de_params)


if __name__ == "__main__":
    # 替换为你的Access数据库文件路径
    db_path_ = r"D:\programData\access\test.accdb"
    # excel_path = r"D:\存档\存档-已导入\BEAU5586740-完结\149507170792--TGBU6890170 箱单-test.xlsx"
    pdf_path_ = r"D:\存档\存档-已导入\CBHU9451440-完结\BL-OOLU2161707750.pdf"
    # pdf_path_ = r"D:\存档\存档-已导入\BEAU5586740-完结\BL-OOLU8811209512.pdf"
    #
    # pdf_info = extractPdfInfo.extractPdfInfo(pdf_path)
    # de 算法参数
    de_params_ = {
        'maxiter': 3000,
        'popsize': 30,
        'mutation': (0.7, 0.9),
        'recombination': 0.7,
        'polish': True,
        'tol': 1e-6
    }

    # 约束
    add_constraint_config = {
        'total_value': (5000, 5500),  # 产品总价值范围
        'tariff_sum': (1300, 1500),  # 关税总和范围
        'weight_ratio': (0, 1.0)  # 重量比率差异范围
    }
    main_handle(db_path=db_path_, pdf_path=pdf_path_, de_params=de_params_, add_constraint_config=add_constraint_config)
